package com.library.dao;



import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.library.servlet.Resource;
import com.mysql.jdbc.Connection;

public class MySQLUserDAO implements UserDAO {

	private Connection conn;
	public MySQLUserDAO(){};
	@Override
	public boolean insertUser(User user) {
		User one = findUser(user.getUserId());
		conn = MySQLConnection.getConnection();
		boolean res = false;
		if ((one == null) && (conn != null)){// khi chua co
			String querry = "INSERT INTO users (userid,password,email,name,address,phone) VALUES('"
					+ user.getUserId()
					+ "','"
					+ user.getPassword()
					+ "','"
					+ user.getEmail()
					+ "','"
					+ user.getName()
					+ "','"
					+ user.getAddress() + "','" + user.getPhone() + "')";
			
			try {
				Statement stmt;
				stmt = conn.createStatement();
				stmt.execute(querry);
				res = true;
			} catch (SQLException e) {
				res = false;
			} finally{
				MySQLConnection.closeConnection(conn);
			}
		}
						
		return res;
	}

	@Override
	public boolean removeUser(int id) {
		conn = MySQLConnection.getConnection();
		boolean res = false;
		String querry = "DELETE FROM users WHERE (userid = '"+id+"')";
		if (conn != null){
			Statement st;
			try {
				st = conn.createStatement();
				st.execute(querry);
				res = true;
			} catch (SQLException e) {
				res = false;		
			} finally{
				MySQLConnection.closeConnection(conn);
			}
		}
		return res;
	}

	@Override
	public boolean updateUser(User user) {
		conn = MySQLConnection.getConnection();
		removeUser(user.getUserId());
		boolean kq = false;
		if (insertUser(user))
			kq = true;
		MySQLConnection.closeConnection(conn);
		return kq;
	}

	@Override
	public User findUser(int id) {
		conn = MySQLConnection.getConnection();
		User res = null;
		if (conn!= null){
			String querry = "SELECT * FROM users WHERE userid = '"+id+"'";
			try {
				Statement stmt = conn.createStatement() ;
				ResultSet result = stmt.executeQuery(querry);
				if (result.next()){
					res = new User();
					res.setAddress(result.getString("address"));
					res.setPassword(result.getString("password"));
					res.setEmail(result.getString("email"));
					res.setPhone(result.getString("phone"));
					res.setName(result.getString("name"));
					res.setUserId(id);

				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally{
				MySQLConnection.closeConnection(conn);
			}

		}
		return res;
	}
	@Override
	public ArrayList<BookTitle> getBorrowedBook(int userid, String status) {
		String sqlquery = "SELECT * from booktitles where ( booktitleid in (" +
				"select booktitleid from bookitems where (bookid in (" +
		"select bookitemid from lendings where((userid = '"+userid+ "') AND (status = '" + status+"' ))))))";
		conn = MySQLConnection.getConnection();
		ArrayList<BookTitle> result = new ArrayList<BookTitle>();
		if (conn != null){
			try {
				Statement st = conn.createStatement();
				ResultSet res = st.executeQuery(sqlquery);
				while (res.next()){
					BookTitle temp = new BookTitle();
					int bookTitleId = Integer.parseInt(res.getString("booktitleid"));
					temp.setBookTitleId(bookTitleId);
					temp.setAuthor(res.getString("author"));
					temp.setDescription(res.getString("description"));
					temp.setPublisher(res.getString("publisher"));
					temp.setTitle(res.getString("title"));
					result.add(temp);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally{
				MySQLConnection.closeConnection(conn);
			}
		}
		return result;
	}
	@Override
	public int NoOfBorrowtedBook(int userid, String status) {
		/*kiem tra so sach dang muon*/
		int num = 0;
		String query = "select count(lendingid) from lendings where ((userid = "+userid+") AND (status = '"+Resource.CONTINUOUS+"'))";
		conn = MySQLConnection.getConnection();
		if (conn != null){
			try {
				Statement st = conn.createStatement();
				ResultSet res = st.executeQuery(query);
				while (res.next()){
					num = res.getInt(1);
				}
			} catch (SQLException e) {
				
				e.printStackTrace();
			} finally{
				MySQLConnection.closeConnection(conn);
			}
			
		}
		if (num > 0)
			num = num -1;
		return num;
	}
	@Override
	/**
	 * return true neu co sach qua han
	 */
	public boolean checkOverdateBook(int id) {
		DAOFactory factory = new MySQLDAOFactory();
		LendingDAO lendingDAO = factory.getLendingDAO();
		ArrayList<Lending> lendingList = lendingDAO.getUserLending(id, Resource.CONTINUOUS);
		boolean still = false;
		for(Lending lend : lendingList){
			java.sql.Date lendingDate = lend.getDate();
			java.util.Date convertedDate = new java.util.Date(lendingDate.getTime());
			java.util.Date now = new java.util.Date();
			
			int diff = (int)( (now.getTime() - convertedDate.getTime()) 
	                 / (1000 * 60 * 60 * 24) );
			//System.out.println(diff);
			if (diff > Resource.LENDINGPERIOD){
				still = true;
				break;
			}
		}
		
		return still;
	}
}
